﻿//----------------------------------------------------
//    版权：
//    作者：
//建立时间：2012-09-11 00:00:33
//    描述：
//----------------------------------------------------
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Entity;

using System.Linq;
using System.Text;
using WinnerCMS.Common;
using WinnerCMS.Model;

namespace WinnerCMS.Data
{
    /// <summary>
    /// 数据访问层CMS_Model。
    /// </summary>
    public class ModelDal
    {
        #region  添加数据
        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public byte Add(WinnerCMS.Model.Model model)
        {
            using (Entities db = new Entities())
            {
                byte newModelId = db.Models.Max(x => x.Id);
                if (newModelId < 100)
                {
                    model.Id = 100;
                }
                else
                {
                    model.Id = (byte)(newModelId + 1);
                }
                model.Hide = false;
                db.Models.Add(model);
                db.SaveChangesAsync().Wait();

                return model.Id;
            }
        }
        #endregion

        #region  修改数据
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="model"></param>
        public void Edit(WinnerCMS.Model.Model model)
        {
            using (Entities db = new Entities())
            {
                db.Models.Attach(model);
                db.Entry(model).State = EntityState.Modified;
                db.SaveChangesAsync().Wait();
            }
        }
        #endregion

        #region  删除数据
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="Id"></param>
        public void Delete(byte Id)
        {
            using (Entities db = new Entities())
            {
                var q = db.Models.First(x => x.Id == Id);
                db.Models.Remove(q);

                db.SaveChangesAsync().Wait();
            }
        }

        #endregion

        #region 取得所有字段
        /// <summary>
        /// 取得所有字段
        /// </summary>
        /// <param name="ModelId"></param>
        /// <returns></returns>
        public object GetFields(byte ModelId)
        {
            using (Entities db = new Entities())
            {
                string table = db.Models.Where(x => x.Id == ModelId).Select(x => x.DataTable).First();
                object obj = db.Database.SqlQueryForDynamic(@" Select  a.name as COLUMN_NAME
                from syscolumns a, systypes b, sysObjects c 
                 where a.xtype = b.xusertype 
                 and a.id = c.id 
                 and c.name = (Select DataTable from CMS_Model where Id=" + ModelId + ")");

                return obj;
            }
        }
        #endregion

        #region 系统的模型统计
        /// <summary>
        /// 系统的模型统计
        /// </summary>
        /// <returns></returns>
        public DataSet ModelsInTheCMS()
        {
            MyStringBuilder winner = new MyStringBuilder();
            foreach (var v in GetList())
            {
                winner.AppendLine("Select");
                winner.AppendFormat("({0}) as Id,\r\n", v.Key);
                winner.AppendFormat("(Select ModelName From CMS_Model Where Id={0}) as ModelName,\r\n", v.Key);
                winner.AppendFormat("(Select Count(Id) From {0} where IsChecked=1) as PassContentCount,\r\n", v.Value.DataTable);
                winner.AppendFormat("(Select Count(Id) From {0} where ContentState=1) as UnPassContentCount,\r\n", v.Value.DataTable);
                winner.AppendFormat("(Select Count(Id) From {0} where IsDeleted=1) as DeleteContentCount\r\n", v.Value.DataTable);
                winner.AppendLine("Union ALL");
            }
            winner.TrimEnd("\r\nUnion ALL");
            winner.AppendLine(" Order by Id");

            return SQLBaseV2.ExecuteDataSet(winner.ToString());
        }
        #endregion

        #region  取得数据
        /// <summary>
        /// 取得数据
        /// </summary>
        /// <returns></returns>
        public Dictionary<byte, WinnerCMS.Model.Model> GetList()
        {
            using (Entities db = new Entities())
            {
                db.Configuration.ProxyCreationEnabled = false;
                db.Configuration.AutoDetectChangesEnabled = false;
                var q = from x in db.Models
                        where x.Hide == false
                        orderby x.Id ascending
                        select x;
                return q.ToDictionary(x => x.Id, x => x);
            }
        }
        #endregion

        #region 数据库表数据量统计
        /// <summary>
        /// 数据库表数据量统计
        /// </summary>
        /// <param name="tableName">数据库表</param>
        /// <returns></returns>
        public int GetDBTableCount(string tableName)
        {
            using (Entities db = new Entities())
            {
                return db.Database.SqlQuery<int>("P_CMS_DBTableCount {0}", tableName).First();

            }
        }
        #endregion

        #region 数据库所有表数据量统计
        /// <summary>
        /// 数据库表数据量统计
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, int> GetDBCount()
        {
            using (Entities db = new Entities())
            {
                var query = db.Database.SqlQuery<KeyValue<string, int>>("P_CMS_DBCount")
                             .ToDictionary(x => x.Key, x => x.Value);
               
                return query;
            }
        }
        #endregion
    }
}